
import pandas as pd


sel=input('1：恒生佣金模板, 2:络町佣金模板')
if sel=='1':
    佣金模板=pd.read_excel("./PBFee.xlsx",sheet_name="恒生")
    产品信息=佣金模板.columns[1]

    公司序号=产品信息.split("_")[1]
    产品序号=产品信息.split("_")[2]
    单元序号=产品信息.split("_")[3]
    佣金类型=产品信息.split("_")[4]

    # 佣金生成数据=pd.DataFrame(columns=["公司序号","产品序号","单元序号","交易市场","业务分类","证券类别","费用子类","证券代码","费用类别","计费方式","费率","最低费用","最高费用","合笔方式","委托方向"])
    客户佣金信息=[]
    for index,row in 佣金模板.iterrows():
        if row["科目"]=="A股" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,1,"!","",1,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-股票-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,1,"!","",1,2,row[产品信息],5,0,1,"!"])   #深交所A-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,1,"!","",4,2,row[产品信息],5,0,1,"!"])  #上交所A-交易所业务-股票-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,1,"!","",4,2,row[产品信息],5,0,1,"!"])  #深交所A-交易所业务-股票-佣金
        elif row["科目"]=="基金" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F","!","",1,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F","!","",1,2,row[产品信息],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,2,"!","",1,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-封闭式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,2,"!","",1,2,row[产品信息],5,0,1,"!"])   #深交所A-交易所业务-封闭式基金-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F","!","",4,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F","!","",4,2,row[产品信息],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,2,"!","",4,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-封闭式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,2,"!","",4,2,row[产品信息],5,0,1,"!"])   #深交所A-交易所业务-封闭式基金-佣金
        elif row["科目"]=="债券ETF" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F",3,"",1,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F",3,"",1,2,row[产品信息],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F",3,"",4,2,row[产品信息],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F",3,"",4,2,row[产品信息],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-佣金

        elif row["科目"]=="债券" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,3,"!","",1,2,row[产品信息],1,0,1,"!"])   #上交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,4,"!","",1,2,row[产品信息],1,0,1,"!"])   #上交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,6,"!","",1,2,row[产品信息],1,0,1,"!"])   #上交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"S","!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"T","!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,3,"!","",1,2,row[产品信息],0,0,1,"!"])   #深交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,4,"!","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,6,"!","",1,2,row[产品信息],1,0,1,"!"])   #深交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"S","!","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"T","!","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",3,"!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",4,"!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",6,"!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","S","!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","T","!","",1,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",3,"!","",1,2,row[产品信息],0,0,1,"!"])   #深交所A-大宗交易-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",4,"!","",1,2,row[产品信息],0,0,1,"!"])    #深交所A-大宗交易-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",6,"!","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","S","!","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","T","!","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易-地方债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,3,"!","",4,2,row[产品信息],1,0,1,"!"])   #上交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,4,"!","",4,2,row[产品信息],1,0,1,"!"])   #上交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,6,"!","",4,2,row[产品信息],1,0,1,"!"])   #上交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"S","!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"T","!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,3,"!","",4,2,row[产品信息],0,0,1,"!"])   #深交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,4,"!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,6,"!","",4,2,row[产品信息],1,0,1,"!"])   #深交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"S","!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"T","!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",3,"!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",4,"!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",6,"!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","S","!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","T","!","",4,2,row[产品信息],1,0,1,"!"])  #上交所A-固收平台-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",3,"!","",4,2,row[产品信息],0,0,1,"!"])   #深交所A-大宗交易-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",4,"!","",4,2,row[产品信息],0,0,1,"!"])    #深交所A-大宗交易-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",6,"!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","S","!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","T","!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易-地方债-交易费
        elif row["科目"]=="债券转股回售" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
        elif row["科目"]=="上海债券转股" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
        elif row["科目"]=="上海可转债回售" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
        elif row["科目"]=="上海债券回售" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费


        elif row["科目"]=="深市可转债" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"g","",1,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-分离债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"g","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-分离债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"g","",4,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"g","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金

        elif row["科目"]=="沪市可转债" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"g","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"g","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"g","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"g","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
        elif row["科目"]=="深市可交换债" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,5,"",1,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,6,"",1,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"n","",1,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,5,"",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,6,"",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"n","",1,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,5,"",4,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,6,"",4,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"n","",4,2,row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,5,"",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,6,"",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"n","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金

        elif row["科目"]=="沪市可交换债" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,5,"",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,6,"",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"n","",1,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,5,"",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,6,"",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"n","",1,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费

            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,5,"",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,6,"",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"n","",4,2,row[产品信息],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,5,"",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,6,"",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"n","",4,2,row[产品信息],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金

        elif row["科目"]=="新三板" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"j",1,1,"!","",1,2,row[产品信息],0,0,1,"!"])  #股转市场-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"j",1,1,"!","",4,2,row[产品信息],0,0,1,"!"])  #股转市场-交易所业务-股票-佣金

        elif row["科目"]=="北交所A股" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"r",1,1,"!","",1,2,row[产品信息],0,0,1,"!"])  #北交所-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"r",1,1,"!","",4,2,row[产品信息],0,0,1,"!"])   #北交所-交易所业务-股票-佣金
        elif row["科目"]=="深港通" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"o",1,1,"!","",1,2,row[产品信息],2,0,1,"!"])  #港股通深-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"o",1,1,"!","",4,2,row[产品信息],2,0,1,"!"])  #港股通深-交易所业务-股票-佣金

        elif row["科目"]=="沪港通" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"n",1,1,"!","",1,2,row[产品信息],2,0,1,"!"]) #港股通沪-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"n",1,1,"!","",4,2,row[产品信息],2,0,1,"!"]) #港股通沪-交易所业务-股票-佣金
        elif row["科目"]=="上海债券回购" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204001,1,2,0.01*row[产品信息],0,0,1,"!"])  #上交所A-交易所业务-债券回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204002,1,2,0.02*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204003,1,2,0.03*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204004,1,2,0.04*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204007,1,2,0.05*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204014,1,2,0.1*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204028,1,2,0.2*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204091,1,2,0.3*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204182,1,2,0.3*row[产品信息],0,0,1,"!"])
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204001,4,2,0.01*row[产品信息],0,0,1,"!"])  #上交所A-交易所业务-债券回购-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204002,4,2,0.02*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204003,4,2,0.03*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204004,4,2,0.04*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204007,4,2,0.05*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204014,4,2,0.1*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204028,4,2,0.2*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204091,4,2,0.3*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204182,4,2,0.3*row[产品信息],0,0,1,"!"])
        elif row["科目"]=="深圳债券回购" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131810,1,2,0.01*row[产品信息],0,0,1,"!"]) #深交所A-交易所业务-债券回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131811,1,2,0.02*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131800,1,2,0.03*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131809,1,2,0.04*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131801,1,2,0.05*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131802,1,2,0.1*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131803,1,2,0.2*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131805,1,2,0.3*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131806,1,2,0.3*row[产品信息],0,0,1,"!"])
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131810,4,2,0.01*row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-债券回购-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131811,4,2,0.02*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131800,4,2,0.03*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131809,4,2,0.04*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131801,4,2,0.05*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131802,4,2,0.1*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131803,4,2,0.2*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131805,4,2,0.3*row[产品信息],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131806,4,2,0.3*row[产品信息],0,0,1,"!"])
        elif row["科目"]=="质押式协议回购" and row[产品信息]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","-","!","",1,2,row[产品信息],0,0,1,"!"])  #上交所A-上交所固定收益平台-协议质押式回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","-","!","",1,2,row[产品信息],0,0,1,"!"])  #上交所A-交易所业务-协议质押式回购-佣金
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","-","!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-协议质押式回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","-","!","",4,2,row[产品信息],0,0,1,"!"])  #深交所A-交易所业务-协议质押式回购-佣金


    生成数据=pd.DataFrame(客户佣金信息,columns=["公司序号","产品序号","单元序号","交易市场","业务分类","证券类别","费用子类","证券代码","费用类别","计费方式","费率","最低费用","最高费用","合笔方式","委托方向"])
    df_no_duplicates = 生成数据.drop_duplicates(keep="last")
    df_no_duplicates.to_excel("./生成结果.xlsx",index=False)
if sel=='2':
    佣金模板=pd.read_excel("./PBFee.xlsx",sheet_name="络町")
    产品信息=佣金模板.columns[1]
    机构编号=产品信息.split("_")[1]
    是否信用户=产品信息.split("_")[2]
    费用模板名称="佣金模板"+机构编号
    # 佣金生成数据=pd.DataFrame(columns=["公司序号","产品序号","单元序号","交易市场","业务分类","证券类别","费用子类","证券代码","费用类别","计费方式","费率","最低费用","最高费用","合笔方式","委托方向"])
    客户佣金信息=[]
    for index,row in 佣金模板.iterrows():
        if row["科目"]=="A股" and row[产品信息]!=0:
            if 是否信用户=="普通户":
                for 交易所 in ["上海证券交易所","深圳证券交易所"]:
                    for 交易行为 in ["买入","卖出"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,"股票","","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,5,"Excel导入"])

            elif 是否信用户=="信用户":
                for 交易所 in ["上海证券交易所","深圳证券交易所"]:
                    for 交易行为 in ["买入","卖出","融资买入","融券卖出","买券还券","卖券还款"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,"股票","","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,5,"Excel导入"])


        elif row["科目"]=="基金" and row[产品信息]!=0:
            if 是否信用户=="普通户":
                for 交易所 in ["上海证券交易所","深圳证券交易所"]:
                    for 交易行为 in ["买入","卖出"]:
                        for 基金品种 in ["ETF基金","LOF基金","跨境ETF基金","跨境LOF基金","封闭式基金","黄金ETF基金","债券ETF基金","普通开放式基金"]:
                            客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,5,"Excel导入"])

            elif 是否信用户=="信用户":
                for 交易所 in ["上海证券交易所","深圳证券交易所"]:
                    for 交易行为 in ["买入","卖出","融资买入","融券卖出","买券还券","卖券还款"]:
                        for 基金品种 in ["ETF基金","LOF基金","跨境ETF基金","跨境LOF基金","封闭式基金","黄金ETF基金","债券ETF基金","普通开放式基金"]:
                            客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,5,"Excel导入"])
        elif row["科目"]=="债券" and row[产品信息]!=0:
            for 交易所 in ["上海证券交易所"]:
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["国债","企业债","公司债"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,1,"Excel导入"])
            for 交易所 in ["深圳证券交易所"]:   #深圳0最低消
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["国债","企业债","公司债"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,0,"Excel导入"])
        elif row["科目"]=="深市可转债" and row[产品信息]!=0:
            for 交易所 in ["深圳证券交易所"]:   #深圳0最低消
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["可转债"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,0,"Excel导入"])
        elif row["科目"]=="沪市可转债" and row[产品信息]!=0:
            for 交易所 in ["上海证券交易所"]:
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["可转债"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,1,"Excel导入"])
        elif row["科目"]=="北交所A股" and row[产品信息]!=0:
            for 交易所 in ["北京证券交易所"]:
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["股票"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,0,"Excel导入"])
        elif row["科目"]=="深港通" and row[产品信息]!=0:
            for 交易所 in ["深港通证券交易所"]:
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["股票"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,5,"Excel导入"])
        elif row["科目"]=="沪港通" and row[产品信息]!=0:
            for 交易所 in ["沪港通证券交易所"]:
                for 交易行为 in ["买入","卖出"]:
                    for 基金品种 in ["股票"]:
                        客户佣金信息.append([机构编号,费用模板名称,"类型","Excel导入",交易所,基金品种,"","交易佣金",交易行为,row[产品信息]/1000,0,0,0,0,5,"Excel导入"])


    生成数据=pd.DataFrame(客户佣金信息,columns=["机构编号","费用模板名称","费用模板类别","费用模板备注信息","交易市场","证券类型","证券代码","证券费用类型","订单方向","金额比例","金额固定额","面值比例","面值固定额","最高费用","最低费用","费用模板明细备注信息"])
    df_no_duplicates = 生成数据.drop_duplicates()
    df_no_duplicates.to_excel("./生成结果.xlsx",index=False)


print("已生成")
input("Press Enter to exit…")

# oneData=fullData.drop_duplicates(subset=['发生日期',"操作员编号"])
# fullData.to_csv(r"C:\Users\AXZQ\Desktop\新建文件夹\恒生PB\all.csv")
# oneData.to_csv(r"C:\Users\AXZQ\Desktop\新建文件夹\恒生PB\one.csv")
